#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author: long
# @Date:   2019-08-02 20:03:28
# @Last Modified by:   ystlong
# @Last Modified time: 2019-08-04 22:39:59

import sqlite3
import codecs
import sys



# CREATE TABLE WORDS (id TEXT PRIMARY KEY NOT NULL, word TEXT, stem TEXT, lang TEXT, category INTEGER DEFAULT 0, timestamp INTEGER DEFAULT 0, profileid TEXT);
# CREATE INDEX wordprofileid ON WORDS (profileid);
# CREATE TABLE LOOKUPS (id TEXT PRIMARY KEY NOT NULL, word_key TEXT, book_key TEXT, dict_key TEXT, pos TEXT, usage TEXT, timestamp INTEGER DEFAULT 0);
# CREATE INDEX lookupwordkey ON LOOKUPS (word_key);
# CREATE INDEX lookupbookkey ON LOOKUPS (book_key);
# CREATE TABLE BOOK_INFO (id TEXT PRIMARY KEY NOT NULL, asin TEXT, guid TEXT, lang TEXT, title TEXT, authors TEXT);
# CREATE TABLE DICT_INFO (id TEXT PRIMARY KEY NOT NULL, asin TEXT, langin TEXT, langout TEXT);
# CREATE TABLE METADATA (id TEXT PRIMARY KEY NOT NULL, dsname TEXT, sscnt INTEGER, profileid TEXT);
# CREATE TABLE VERSION (id TEXT PRIMARY KEY NOT NULL, dsname TEXT, value INTEGER);
# sqlite> select a.usage, b.title from lookups as a join book_info as b;

class PyGlossaryDB(object):
	"""docstring for PyGlossaryDB"""
	def __init__(self, sql_file, dict_db_file):
		super(PyGlossaryDB, self).__init__()
		self.sql_file = sql_file
		self.conn = sqlite3.connect(dict_db_file)

	def init(self):
		with open(self.sql_file) as f:
			for line in f:
				line = line.strip()
				if line == "":
					continue
				self.conn.execute(line)
		self.conn.commit()

	def query_word(self, stem):
		print stem
		stem = stem.replace("'", "''")
		sql = u"select w,m from word where w = '{stem}' or w like '%|{stem}' or w like '{stem}|%' or w like '%|{stem}|%' COLLATE NOCASE".format(stem=stem)
		res = self.conn.execute(sql).fetchall()
		rs = []
		for row in res:
			r = u"%s<br>%s"%(row[0], row[1])
			r.replace("\t", " ")
			rs.append(r)
			# print stem, "===", row[0]
		return "<br>".join(rs)

class KindleDict(object):
	"""docstring for KindleDict"""
	def __init__(self, db_file, dict_query):
		super(KindleDict, self).__init__()
		self.db_file = db_file
		if db_file != None:
			self.conn = sqlite3.connect(db_file)
		self.dict_query = dict_query

	def format_res(self, rs):
		for row in rs:
			for col in row:
				print col, 
			print ""

	def wrap_word_merge(self, db_rs):
		merge_rs = {}
		for row in  db_rs:
			word = row[0]
			stem = row[1]
			lang = row[2]
			book_title = row[3]
			usage = row[4]
			if not merge_rs.has_key(stem):
				merge_rs[stem] = {"word":set(), "lang":lang, "book_title":book_title, "usage":list()}
			word_info = merge_rs[stem]
			word_info["word"].add(word)
			word_info["usage"].append(usage)
		return merge_rs

	def query_mean(self, stem):
		mean = self.dict_query.query_word(stem)
		# print mean
		if mean.strip() == "":
			return "no mean [%s]"%(stem)
		return mean

	def format_anki_html_dict(self, rs, out_file):
		"""将单词列表格式化为anki-html格式文件
		
		rs为单词数组，stem单词原型
		
		Arguments:
			rs {dict} -- {stem:{"word":[word], "usage":[usage]}}
			out_file {[type]} -- [description]
		
		Returns:
			[type] -- [description]
		"""
		ofs = codecs.open(out_file, "w", "utf-8")
		def wrap_lookup_word(org_str, word):
			r_word = "<b><u>%s</u></b>"%(word)
			return org_str.replace(word, r_word)
		keys = rs.keys()
		keys.sort()
		for stem in keys:
			words = rs[stem]
			word = list(words["word"])[0]
			format_usage = ""
			for usage in words["usage"]:
				format_usage += wrap_lookup_word(usage, word)
				format_usage += "<br />"

			mean = self.query_mean(stem)
			anki_str = u"%s (%s)\t%s<hr /><br />%s\n"%(
				stem, word, format_usage,  mean)
			ofs.write(anki_str)
		ofs.close()
		pass

	def query_book(self):
		rs = self.conn.execute("select lang, title from book_info")
		self.format_res(rs)

	def query_word(self, book_title):
		sql = """select a.word, a.stem, a.lang, c.title, b.usage from
			words as a
				join lookups as b on b.word_key=a.id
				join book_info as c on b.book_key=c.id where c.title='%s'
			"""%(book_title)
		rs = self.conn.execute(sql).fetchall()
		print("found word %d item"%(len(rs)))
		# self.format_res(rs)
		return self.wrap_word_merge(rs)

def init(dict_sql_file):
	# the dict sql file from pyglossary tool caonvert to sql format
	# see https://github.com/ilius/pyglossary
	dict_db_file = "dict.db"
	dict_db = PyGlossaryDB(dict_sql_file, dict_db_file)
	dict_db.init()
	print("init finish")

def anki_html_data(kindel_db_file, book_title, out_file):
	dict_query = PyGlossaryDB(None, "dict.db")
	kd = KindleDict(kindel_db_file, dict_query)
	rs = kd.query_word(book_title)
	kd.format_anki_html_dict(rs, out_file)

def query_book(kindel_db_file):
	kd = KindleDict(kindel_db_file, None)
	kd.query_book()

def word_txt_to_anki(txt_file, out_file):
	"""将英语单词列表文件转换为anki-html格式
	
	txt_file文本格式为每行一个单词（词组），后面可接tab作为解释
	
	Arguments:
		txt_file {string} -- word列表文件路径
	"""

	dict_query = PyGlossaryDB(None, "dict.db")
	words = {}
	with codecs.open(txt_file, "r", "utf-8") as f:
		for line in f:
			line = line.strip()
			if line == "":
				continue
			sp = line.split()
			stem = sp[0]
			usage = ""
			if len(sp) == 2:
				usage = sp[1]
			if not words.has_key(stem):
				words[stem] = {"word": set(), "usage":[]}
			word = words[stem]
			word["word"].add(stem)
			word["usage"].append(usage)
	# format to anki
	kd = KindleDict(None, dict_query)
	kd.format_anki_html_dict(words, out_file)



def usage():
	usage_str = """
	usage: 
	    {exec_str} init dict_sql_file
	    {exec_str} book kindel_db_file
	    {exec_str} word kindel_db_file book_title anki_txt_out_file
	    {exec_str} txt txt_word_list_file anki_txt_out_file
	""".format(exec_str=__file__)
	print(usage_str)

def main():
	try:
		cmd = sys.argv[1]
		if cmd == "init":
			init(sys.argv[2])
		elif cmd == "book":
			kindel_db_file = sys.argv[2]
			query_book(kindel_db_file)
		elif cmd == "word":
			kindel_db_file = sys.argv[2]
			book = sys.argv[3]
			if len(sys.argv) == 5:
				out_file = sys.argv[4]
			else:
				out_file = "anki-%s.txt"%(book)
			anki_html_data(kindel_db_file, book, out_file)
		elif cmd == "txt":
			txt_word_list_file = sys.argv[2]
			out_file = sys.argv[3]
			word_txt_to_anki(txt_word_list_file, out_file)
		else:
			usage()
	except Exception as e:
		usage()
		raise 

if __name__ == '__main__':
	main()

def test():
	db_file="/home/long/vocab.db"
	dict_db = PyGlossaryDB("/home/long/work/pyglossary/oxford-format.sql", "dict_oxford.db")
	kd = KindleDict(db_file, dict_db)	
	# rs = kd.query_book()
	rs = kd.query_word()
	kd.format_anki_html_dict(rs)


